import ui_mainw
from PyQt5.QtCore import QAbstractTableModel,QModelIndex
from PyQt5.QtCore import QDateTime, QTime,Qt,QTimer
from PyQt5.QtSql import QSqlQueryModel,QSqlQuery
from PyQt5.QtWidgets import QAbstractItemView, QMainWindow, QApplication, QMessageBox, QCheckBox, QHBoxLayout,QFileDialog,QLabel,QPushButton,QCompleter
from PyQt5.QtWidgets import QFileDialog,QStyle
from PyQt5 import  QtWidgets,QtSql
import os
import sys
import csv
import config_json
import openpyxl
import sqlite3
import json
import traceback
import getpath
import subprocess
from xml.etree import ElementTree as ET 
class MyModel(QSqlQueryModel):
    def __init__(self,parent):
        super().__init__(parent)
        self.beizhu={}
    def setQuery(self,cmd):
        self.beizhu={}
        super().setQuery(cmd)
        pass
    def setData(self, index, value, role = Qt.EditRole):
        logging.info("setData")
        cols=self.columnCount(QModelIndex())
        row = index.row()
        column = index.column()
        if column==cols-1 and role == Qt.EditRole:
            self.beizhu[row] = value
            self.dataChanged.emit(index,index,[role])#todo here
            return True
        return super().setData(index,value,role)     
    def flags(self, index):
        # logging.info("flags")
        fl=super().flags(index)
        if index.column() == self.columnCount( QModelIndex())-1:
            fl |= Qt.ItemIsEditable
        # logging.info(dir(self))
        # logging.info(index.column(),self.columnCount( QModelIndex()))
        return fl
    def columnCount(self, parent):
        count=super().columnCount(parent)
        return count+1
        # return self.table.columnCount()
    # def headerData(self, section, orientation, role):
    #     hd=super().headerData(section,orientation,role)
    #     return hd
    def data(self, index, role):
        cols=self.columnCount(QModelIndex())
        row = index.row()
        column = index.column()
        if role == Qt.EditRole or role==Qt.DisplayRole:
            if column==cols-1:
                try:
                    return self.beizhu[row]
                except KeyError:
                    return None
        return super().data(index,role)
def inList(one, l):
    for i in range(len(l)):
        logging.info(one,l[i])
        if one in l[i]:
            return i
    return -1
def removeLast(str1):
    arr=str1.split("//")
    return "//".join(arr[:-1])
# class Mydialect(csv.Dialect):
#     delimiter = ","
#     escapechar = '\\'
#     doublequote = True
#     skipinitialspace = True
#     lineterminator = '\r\n'
#     quoting = csv.QUOTE_ALL
#     quotechar = '"'
def is_int(a_string):
    try:
        int(a_string)
        return True
    except ValueError:
        return False
def is_float(a_string):
    try:
        float(a_string)
        return True
    except TypeError:
        return False
    except ValueError:
        return False
def timestr():
    db = QDateTime.currentDateTime()
    db_str = db.toString("yyyy_MM_dd_hh_mm_ss")
    return db_str
def convT(d):
    (y,m,d)=d.split(":")
    if len(y)==1:
        y="0"+y
    if len(m)==1:
        m="0"+m
    if len(d)==1:
        d="0"+d
    return y+":"+m+":"+d

def convD(d):
    (y,m,d)=d.split("-")
    if len(m)==1:
        m="0"+m
    if len(d)==1:
        d="0"+d
    return y+"-"+m+"-"+d
def convert_time(t):
    r=t.replace("/","-")+":00"
    (d,t)=r.split(" ")
    return convD(d)+" "+convT(t)
class DoubleDelegate(QtWidgets.QStyledItemDelegate):
    def paint(self,painter, option, index):
        #注意，此时index是logicIndex（model坐标），我们可以通过拖拽表头项交换列来测试
        #QStyle会根据option的属性进行绘制，我们也可以不使用QStyle的规则，完全自定义
        opt = option
        #去掉焦点 setFocusPolicy(Qt::NoFocus);
        # opt.state &= ~QStyle.State_HasFocus
        #参照源码实现了自己的initStyleOption
        # self.initMyStyleOption(opt, index)
        value = index.model().data(index,Qt.EditRole)
        if is_float(value):
            opt.text = "%0.6f" % value
        else:
            opt.text = value
        widget = opt.widget
        if widget:
            style = widget.style() 
        else:
            style= QApplication.style()
        style.drawControl(QStyle.CE_ItemViewItem, opt, painter, widget)    
class MainW(QMainWindow):
    ui = ui_mainw.Ui_MainWindow()
    # def nativeEvent(self, eventType, message):
    #     r=super().nativeEvent(eventType, message)
    #     if sys.platform=="linux":
    #         return r
    #     # logging.info(eventType)
    #     if eventType == "windows_generic_MSG":
    #         msg = ctypes.wintypes.MSG.from_address(message.__int__())
    #         # logging.info(msg.message,win32con.WM_COPYDATA)
    #         if msg.message == win32con.WM_COPYDATA:
    #             # mycallback(msg.hWnd, msg.lParam)
    #             # logging.info("===========================")
    #             # CopyDataStruct cds = (CopyDataStruct)e.GetLParam(typeof(CopyDataStruct));
    #             # textBox1.Text = textBox1.Text+ cds.lpData.ToString()+"\r\n";  /
    #             # logging.info(msg,dir(msg))
    #             # logging.info(dir(ctypes.wintypes.LPARAM))
    #             # logging.info(dir(msg.lParam),msg.lParam)
    #             pCDS = ctypes.cast(msg.lParam, PCOPYDATASTRUCT)
    #             s = ctypes.string_at(pCDS.contents.lpData).decode("gbk")
    #             # s=s.encode("utf-8")
    #             # logging.info(s,type(s),dir(s))
    #             self.ui.textEdit.insertPlainText(s + "\n")
    #             scrollbar = self.ui.textEdit.verticalScrollBar()
    #             scrollbar.setSliderPosition(scrollbar.maximum())
    #             if s=="end":
    #                 self.refresh_grid1()
    #             else:
    #                 # cmd=copy.copy(s)
    #                 try:
    #                     q=QtSql.QSqlQuery()
    #                     q.prepare(s)
    #                     r1=q.exec_()    
    #                     if not r1:
    #                         logging.info(q.lastError())   
    #                 except TypeError as e:
    #                     logging.info(e)
    #     return r
    def closeEvent(self, event):
        config_json.write_setting()
        event.accept()
    def set_database(self):
        self.connection = QtSql.QSqlDatabase.addDatabase("QSQLITE")  # ', connID);
        self.connection.setDatabaseName(":memory:")#
        # self.connection.setDatabaseName(os.path.join(getpath.getpath(),"data.sqlite"))
        if not self.connection.open():
            logging.info("open failed")
            return False
        else:
            q=QSqlQuery()
            q.prepare("""CREATE TABLE Result (
    id       INTEGER  PRIMARY KEY AUTOINCREMENT
                      NOT NULL,
    time     DATETIME,
    sampleid TEXT,
    m        REAL,
    flux     REAL,
    LC        REAL,
    LS        REAL,
    LO        REAL,
    LN        REAL,
    LH        REAL,
    HC        REAL,
    HS        REAL,
    HO        REAL,
    HN        REAL,
    HH        REAL
)""");
            logging.info(dir(q))
            q.exec_()
        return True

    def __init__(self, parent=None):
        QMainWindow.__init__(self, parent)
        self.ct=0
        self.connection = None
        self.ui.setupUi(self)
        config_json.read_setting()
        # if config_json.setting2.value("sblx") not in config_json.setting["sblx_list"]:
        #     config_json.setting2.setValue("sblx",config_json.setting["sblx_list"][0])
        # # self.setWindowTitle("数据上报")#——"+config_json.setting2.value("sblx"))
        # self.label_sblx=QLabel(self)
        # self.label_sblx.setText(config_json.setting2.value("sblx"))
        # self.statusBar().addWidget(self.label_sblx)
        # self.set_database()
        # self.ncol=0
        # self.m_doubleDelegate=DoubleDelegate()
        # self.ids=[]
        # self.model = QSqlQueryModel(self)
        # self.ui.tableView.setModel(self.model)
        # self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
        # self.ui.btnSelect.clicked.connect(self.select)
        # self.ui.btnUnselect.clicked.connect(self.unselect)
        # self.ui.btnClear.clicked.connect(self.clear)
        # self.ui.btnLims.clicked.connect(self.lims)
        # self.ui.pushButton_import.clicked.connect(self.import_click)
        # self.ui.pushButton_clearDB.clicked.connect(self.clearDB_click)
        # self.model2 = MyModel(self)
        # self.ui.actionsetting.triggered.connect(self.setting)
        # self.ui.actionffbh.triggered.connect(self.set_ffbh)
        self.ui.action_exit.triggered.connect(self.exit_app)
        # self.ui.actionsave_as.triggered.connect(self.save_as)
        # self.ui.actionsyy.triggered.connect(self.set_syy)
        # self.h = QHBoxLayout(self.ui.groupBox)
        
        # self.genFieldStr()
        # self.gen_idstr()
        # self.model2.setQuery("select " + self.fieldStr + " from result where id in " + self.idStr)
        # self.ui.tableView_report.setModel(self.model2)
        # self.ui.tableView_report.setSelectionBehavior(QAbstractItemView.SelectRows)
        # self.refresh_ffbh()
        # self.refresh_syy()
        # self.refresh_ff2()
        # self.refresh_js2()
        # self.ui.lineEdit_yqbh.setText(str(config_json.setting2.value("yqbh")))
        # # elif config_json.setting2.value("sblx") in ["ncs_cs_db","ncs_onh_db","element_db"]:
        # #     self.ui.pushButton_import.setVisible(False)#setEnabled(False)
        # #     pass
        # self.resetItems()
        # self.ui.cmb_syy.currentIndexChanged[int].connect(self.syy_change)
        # self.ui.cmb_ysry.currentIndexChanged[int].connect(self.ysry_change)
        # self.ui.cmb_ffbh.currentIndexChanged[int].connect(self.ffbh_change)
        # self.ui.cmb_syyqm.currentIndexChanged[int].connect(self.syyqm_change)
        # self.ui.pushButton_last.clicked.connect(self.import_last)
        # self.ui.action.triggered.connect(self.beizhu)
        # self.ui.action_version.triggered.connect(self.version)
        # self.ui.action_upgrade.triggered.connect(self.upgrade)
        # self.ui.action_sync.triggered.connect(self.sync)
        # self.ui.actionQt.triggered.connect(QtWidgets.qApp.aboutQt)
        # self.ui.action_ff2.triggered.connect(self.set_ff2)
        # self.ui.action_js2.triggered.connect(self.set_js2)
        # self.ui.spinBox_day.valueChanged.connect(self.day_change)
        # self.day_change(1)
        # self.ui.action_clip.triggered.connect(self.clip)
        # if config_json.setting2.value("sblx")=="csi":
        #     fpath=os.path.join(os.path.expanduser('~'),"Desktop","report.xls")#be csv extname is not correct
        #     if os.path.exists(fpath):
        #         try:
        #             self.import_csi(fpath)
        #             self.refresh_grid1()
        #         except:
        #             traceback.logging.info_exc()        
    def clip(self):
        clipboard = QApplication.clipboard()
        # f = io.StringIO()
        # f.write(clipboard.text())
        # if config_json.setting2.value("sblx")=="930":
        #     f.seek(0)
        t=clipboard.text()
        logging.info(t)
        if t!="":
            if config_json.setting2.value("sblx") =="930":#,"uni_cs","uni_on"]:
                self.import_text_bytitle(t)#clip board from excel
                self.refresh_grid1()
            # elif config_json.setting2.value("sblx") =="uni_cs":#,"uni_cs","uni_on"]:
            #     self.import_text_uni_cs(t)#clip board from excel
            #     self.refresh_grid1()
            #     pass
            # elif config_json.setting2.value("sblx") =="uni_on":#,"uni_cs","uni_on"]:
            #     self.import_text_uni_on(t)#clip board from excel
            #     self.refresh_grid1()
            #     pass
    def sync(self):
        src_path=r"""\\10.0.114.230\化学分析室\仪器采集\reports\report_import"""
        file_name = os.path.join(src_path,"setting.json")
        logging.info('Reading %s...' % file_name)
        new_setting=json.load(open(file_name,encoding="utf-8"))
        #ffbh_cs
        (left,right)=bjItems.bjitems(config_json.setting["ffbh_cs"],new_setting["ffbh_cs"])
        logging.info(left,right)
        for one in right:
            config_json.setting["ffbh_cs"].append(one)
        for one in left:
            new_setting["ffbh_cs"].append(one)
        #ffbh_onh
        (left,right)=bjItems.bjitems(config_json.setting["ffbh_onh"],new_setting["ffbh_onh"])
        logging.info(left,right)
        for one in right:
            config_json.setting["ffbh_onh"].append(one)
        for one in left:
            new_setting["ffbh_onh"].append(one)
        #syy
        (left,right)=bjItems.bjitems(config_json.setting["syy"],new_setting["syy"])
        logging.info(left,right)
        for one in right:
            config_json.setting["syy"].append(one)
        for one in left:
            new_setting["syy"].append(one)
        #output 
        with open(file_name, 'w',encoding="utf-8") as set_file:
            json.dump(new_setting, set_file, indent=4)
    def version(self):
        # from version import version
        QMessageBox.about(self, '版本号',config_json.setting["version"])#,QMessageBox.Ok ,QMessageBox.Ok)
    def upgrade(self):
        src_path=r"""\\10.0.114.230\化学分析室\仪器采集\reports\report_import"""
        file_name = os.path.join(src_path,"setting.json")
        logging.info('Reading %s...' % file_name)
        new_setting=json.load(open(file_name,encoding="utf-8"))
        new_version=int(new_setting["version"])
        version=int(config_json.setting["version"])
        if new_version>version:
            logging.info("has new_version",new_version)
            r=QMessageBox.question(self,'升级','是否升级',QMessageBox.Yes|QMessageBox.No,QMessageBox.Yes)
            if r==QMessageBox.Yes:
                logging.info("yes")
                self.close()
                args = ["xcopy.exe", "/Y", src_path,getpath.getpath()]
                subprocess.Popen(args, creationflags=subprocess.DETACHED_PROCESS | subprocess.CREATE_NEW_PROCESS_GROUP, stdout=subprocess.PIPE, stderr=subprocess.PIPE, stdin=subprocess.PIPE, close_fds = True)
            else:
                logging.info("no")
        else:
            logging.info("not new_version",new_version)
        pass
    def beizhu(self):
        from dlgbeizhu import Dlgbeizhu
        d = Dlgbeizhu()
        r = d.exec_()
        if r == 1:
            # self.refresh_ffbh()
            # error here:self.m_comboDelegate=EditDelegateQt(config_json.setting["beizhu"])
            pass
        else:
            pass

    def ysry_change(self,at):
        config_json.setting["current"]["ysry"]=at
    def syyqm_change(self,at):
        config_json.setting["current"]["syyqm"]=at
    def ffbh_change(self,at):
        config_json.setting["current"]["ffbh"]=at
    def syy_change(self,at):
        self.ui.cmb_syyqm.setCurrentIndex(at)
        config_json.setting["current"]["syy"]=at

    def clearLayout(self,layout):
        while True:
            child=layout.takeAt(0)
            logging.info(child)
            if child ==None:
                break
            else:
                logging.info(dir(child))
                child.widget().deleteLater()
    def  resetItems(self):
        # logging.info(dir(self.h))
        self.clearLayout(self.h)
        for i in range(len(config_json.setting[config_json.setting2.value("sblx")]["items"])):
            f = config_json.setting[config_json.setting2.value("sblx")]["items"][i]
            c = QCheckBox(f["name"])
            c.setChecked(f["used"])
            self.h.addWidget(c)
            c.pressed.connect(
                lambda val=i: self.field_click(val)
            )
        self.genFieldStr()
        self.refresh_grid()
    #     b=QPushButton(self)
    #     b.setText("click")
    #     self.h.addWidget(b)
    #     b.clicked.connect(self.exe_cmd)

    # def exe_cmd(self):
    #     for cmd in self.cmds:
    #         q=QtSql.QSqlQuery()
    #         q.prepare(cmd)
    #         r1=q.exec_()    
    #         if not r1:
    #             logging.info(q.lastError())   
    def clearDB_click(self):
        cmd="delete from result"
        q=QSqlQuery()
        q.prepare(cmd)
        logging.info(cmd)
        r=q.exec_()
        if not r:
            logging.info(q.lastError())    
        self.refresh_grid1()
    def import_last(self):
        try:
            if config_json.setting2.value("sblx") in ['uni_cs_msg','uni_onh_msg']:
                self.msg_worker()
            else:
                self.import_file(config_json.setting2.value("database"))#实际为 last import file
        except:
            traceback.logging.info_exc()

    def import_click(self):
        if config_json.setting2.value("sblx") in ["ncs_cs_db","ncs_onh_db","element_db"]:
            self.import_last()
        elif config_json.setting2.value("sblx") in ['uni_cs_msg','uni_onh_msg']:
            self.msg_worker()
        else:
            FileDialog = QFileDialog(self)
            FileDirectory = FileDialog.getOpenFileName(self,'select import file')                #选择目录，返回选中的路径
            #logging.info(FileDirectory)
            fname=FileDirectory[0]
            if fname=="":
                pass
            else:
                try:
                    # self.import_file(config_json.setting["database"])
                    config_json.setting2.setValue("database",fname)
                    self.import_file(fname)
                except:
                    traceback.logging.info_exc()
    def import_file(self,fname):
        if config_json.setting2.value("sblx")=="csi":
            self.import_csi(fname)
        elif config_json.setting2.value("sblx") in ["844","leco836_csv"]:
            self.import_844(fname)
        elif config_json.setting2.value("sblx")=="ncs_cs_db":#,"ncs_onh_db"]:
            self.import_ncs_cs(fname)
        elif config_json.setting2.value("sblx")=="ncs_onh_db":#,"ncs_onh_db"]:
            self.import_ncs_onh(fname)
        elif config_json.setting2.value("sblx")=="leco836_xml":
            self.import_836_xml(fname)
        elif config_json.setting2.value("sblx")=="lecoh":
            self.import_file_byfixed(fname)
        elif config_json.setting2.value("sblx")=="horibah":
            self.import_horibah(fname)
        elif config_json.setting2.value("sblx") in ["ncs_on3000"]:
            self.import_on3000(fname)
        elif config_json.setting2.value("sblx") =="uni_cs":
            self.import_uni_cs(fname)
        elif config_json.setting2.value("sblx") =='uni_on':
            self.import_uni_on(fname)
        elif config_json.setting2.value("sblx") =='element_db':
            self.import_element_db(fname)
        else:
            self.import_file_bytitle(fname)
        self.refresh_grid1()
    def msg_worker(self):
        logfile=os.path.join(getpath.getpath(),"log.txt")
        args=[os.path.join(getpath.getpath(),"uni_msg.exe"), str(self.days)]
        logging.info(args)
        # args = ["xcopy.exe", "/Y", src_path,getpath.getpath()]
        subprocess.Popen(args)#,stdout=subprocess.PIPE, stderr=subprocess.PIPE, stdin=subprocess.PIPE)
        # logging.info(r)
        # self.timer = QTimer()
        # self.timer.setInterval(6*1000)  # msecs 100 = 1/10th sec
        # self.timer.timeout.connect(self.refresh_grid1)
        # self.timer.start()
        # QTimer.singleShot(6*1000, self.refresh_grid1)
        # self.refresh_grid1()
        pass
    def import_ncs_cs(self,fname):
        logging.info(fname)
        constr=fname
        conn = sqlite3.connect(constr)
        c = conn.cursor()
        cmd="select sampleid,sampleName,sampleweight,content10,content20,content30,content40  from result where sampleid between '" + self.db_str + "' and '" + self.de_str + "' "
        c.execute(cmd)
        for row in c:
            cmd="""insert into result(sampleid,m,LC,HC,LS,HS,time) values('%s','%s',%s,%s,%s,%s,'%s')""" % (row[1],row[2],row[3],row[4],row[5],row[6],row[0])
            logging.info(cmd)
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())        
    def import_ncs_onh(self,fname):
        logging.info(fname)
        conn = sqlite3.connect(fname)
        c = conn.cursor()
        cmd="select sampleid,sampleName,sampleweight,content10,content20,content30,content40,content50,content60  from result where sampleid between '" + self.db_str + "' and '" + self.de_str + "' "
        c.execute(cmd)
        for row in c:
            lh=row[7]
            hh=row[8]
            if lh==None:
                lh=-1
            if hh==None:
                hh=-1
            cmd="""insert into result(sampleid,m,LO,HO,LN,HN,LH,HH,time) values('%s','%s',%s,%s,%s,%s,%s,%s,'%s')""" % (row[1],row[2],row[3],row[4],row[5],row[6],lh,hh,row[0])
            logging.info(cmd)
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())  
    def import_text_uni_on(self,txt):  
        logging.info(txt)
        data=[]
        for row in txt.split("\n"):
            # logging.info(row)
            data.append(row.split("\t"))
        logging.info(data)
        title=data[0]
        data=data[1:]
        logging.info("title",title)
        logging.info("data",data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            logging.info(f,title)
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    if "'"+f["name"]+"'" in fs:
                        pass#避免重复字段
                    else:
                        fs.append("'"+f["name"]+"'")
                        logging.info(one[f["at"]])
                        if f["name"]=="time":
                            xl_date=one[f["at"]]
                            date1=xlrd.xldate_as_datetime(xl_date, 0)
                            vs.append("'"+date1.strftime("%Y-%m-%d %H:%M:%S")+"'")
                            # vs.append("'"+str(xl_date)+"'")
                        elif f["name"]=="m":
                            vs.append("'"+str(float(one[f["at"]])/1000.0)+"'")
                        else:
                            vs.append("'"+str(one[f["at"]])+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())       

    def import_uni_on(self,fname):
        xlBook = xlrd.open_workbook(fname)
        table=xlBook.sheets()[0]
        logging.info(dir(table))
        
        data=[]
        for i in range(table.nrows):
            r=[]
            for j in range(table.ncols):
                r.append(table.cell(i,j).value)
            data.append(r)
        title=data[0]
        data=data[1:]
        logging.info("title",title)
        logging.info("data",data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            logging.info(f,title)
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    if "'"+f["name"]+"'" in fs:
                        pass#避免重复字段
                    else:
                        fs.append("'"+f["name"]+"'")
                        logging.info(one[f["at"]])
                        if f["name"]=="time":
                            xl_date=one[f["at"]]
                            date1=xlrd.xldate_as_datetime(xl_date, 0)
                            vs.append("'"+date1.strftime("%Y-%m-%d %H:%M:%S")+"'")
                            # vs.append("'"+str(xl_date)+"'")
                        elif f["name"]=="m":
                            vs.append("'"+str(float(one[f["at"]])/1000.0)+"'")
                        else:
                            vs.append("'"+str(one[f["at"]])+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())       
    def import_text_uni_cs(self,txt):  
        logging.info(txt)
        data=[]
        for row in txt.split("\n"):
            # logging.info(row)
            data.append(row.split("\t"))
        logging.info(data)
        self.uni_cs_data(data)
    def import_uni_cs(self,fname):
        from openpyxl import load_workbook
        xlBook = load_workbook(filename = fname)
        table=xlBook.worksheets[0] 
        logging.info(dir(table))
        data=[]
        for row in table.values:
            r=[]
            for one in row:
                if one!=None:
                    r.append(one)
            data.append(r)
        self.uni_cs(data)
    def uni_cs_data(self,data):
        title=data[0]
        data=data[1:]
        logging.info("title",title)
        logging.info("data",data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            logging.info(f,title)
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    if "'"+f["name"]+"'" in fs:
                        pass#避免重复字段
                    else:
                        fs.append("'"+f["name"]+"'")
                        logging.info(one[f["at"]])
                        if f["name"]=="time":
                            xl_date=one[f["at"]]
                            logging.info(xl_date,dir(xl_date))
                            # date1=xlrd.xldate_as_datetime(xl_date, 0)
                            vs.append("'"+str(xl_date)+"'")
                            # vs.append("'"+str(xl_date)+"'")
                        elif f["name"]=="m":
                            vs.append("'"+str(one[f["at"]]/1000.0)+"'")
                        else:
                            vs.append("'"+str(one[f["at"]])+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())  
    def import_on3000(self,fname):
        from openpyxl import load_workbook
        xlBook = load_workbook(filename = fname)
        table=xlBook.worksheets[0] 
        logging.info(dir(table))
        data=[]
        for row in table.values:
            r=[]
            for one in row:
                if one!=None:
                    r.append(one)
            data.append(r)
        title=data[1]
        data=data[2:]
        logging.info("title",title)
        logging.info("data",data)
        # input("hi")
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            logging.info(f,title)
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    if "'"+f["name"]+"'" in fs:
                        pass#避免重复字段
                    else:
                        fs.append("'"+f["name"]+"'")
                        logging.info(one[f["at"]])
                        if f["name"]=="m":
                            vs.append(str(one[f["at"]]))
                        elif f["name"] in ["LO","LN","HO","HN"]:
                            conc=one[f["at"]]
                            if is_float(conc):
                                conc=conc*100.0
                                vs.append(str(conc))
                            else:
                                vs.append(str(one[f["at"]]))
                        else:
                            vs.append("'"+str(one[f["at"]])+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())        
    def import_element_db(self,fname):
        logging.info(fname)
        constr=fname
        conn = sqlite3.connect(constr)
        c = conn.cursor()
        cmd="""select datetime((id-621355968000000000)/10000000.0,"unixepoch") as time,version,blob from AnalysisResults where time between '""" + self.db_str + "' and '" + self.de_str + "' "
        logging.info(cmd)
        c.execute(cmd)
        for row in c:
            # logging.info(row)
            id1=row[0]
            ver1=row[1]
            blob1=row[2]
            str1=blob1.decode("utf_8_sig")
            # str1=str1.split("//")[0]
            str1=removeLast(str1)
            # logging.info(str1)
            r=json.loads(str1)
            # logging.info(r)
            logging.info(id1,r['SampleId'],r['SampleWeight']/1000.0,r['Results'])  
            cmd="""insert into result(sampleid,m,LC,HC,LS,HS,time) values('%s','%s',%s,%s,%s,%s,'%s')""" % (r['SampleId'],r['SampleWeight']/1000.0,r['Results'][0],r['Results'][1],r['Results'][2],r['Results'][3],id1)
            logging.info(cmd)
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())   
    def import_836_xml(self,fname):
        tree = ET.parse(fname) 
        samples = tree.getroot()
        sample_count=len(samples)
        # title=["name","o","n","m"]
        data=[]
        for i in range(sample_count):
            sample_set=samples[i]
            name=sample_set[1].attrib["Value"]
            parallelNu=len(sample_set)-6
            for j in range(parallelNu):
                o=sample_set[6+j][3].attrib["Value"].split(" ")[0]
                n=sample_set[6+j][4].attrib["Value"].split(" ")[0]
                m=sample_set[6+j][0].attrib["Value"].split(" ")[0]
                time=sample_set[6+j][5].attrib["Value"]
                cmd="""insert into result(sampleid,m,LO,LN,time) values('%s','%s',%s,%s,'%s')""" % (name,m,o,n,time)
                logging.info(cmd)
                q=QSqlQuery()
                q.prepare(cmd)
                logging.info(cmd)
                r=q.exec_()
                if not r:
                    logging.info(q.lastError())
    def import_csi(self,fname):
        csv_reader = csv.reader(open(fname,encoding=config_json.setting[config_json.setting2.value("sblx")]["encoding"]))
        data=[]
        for row in csv_reader:
            data.append(row)
        logging.info(data)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            # one=one[0].split('"\t')
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                logging.info(f)
                if is_int(f["at"]):
                    logging.info(f["at"])
                    fs.append("'"+f["name"]+"'")
                    if f["name"]=="m":
                        vs.append(str(float(one[f["at"]].split(" ")[0])/1000.0))
                    else:
                        vs.append("'"+one[f["at"]].strip()+"'")
                else:
                    logging.info("not int")
                    at=inList(f["at"],one)
                    logging.info(at)
                    if at==-1:
                        pass
                    else:
                        fs.append("'"+f["name"]+"'")
                        if f["name"] in ["LC","LS"]:
                            vs.append(one[at].split(" ")[0])
                        else:
                            vs.append("'"+one[at].strip()+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())                    
    def import_file_bycontent(self,fname):
        csv_reader = csv.reader(open(fname,encoding=config_json.setting[config_json.setting2.value("sblx")]["encoding"]))
        data=[]
        for row in csv_reader:
            data.append(row)
        logging.info(data)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            # one=one[0].split('"\t')
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                logging.info(f)
                if is_int(f["at"]):
                    logging.info(f["at"])
                    fs.append("'"+f["name"]+"'")
                    vs.append("'"+one[f["at"]].strip()+"'")
                else:
                    logging.info("not int")
                    at=inList(f["at"],one)
                    logging.info(at)
                    if at==-1:
                        pass
                    else:
                        fs.append("'"+f["name"]+"'")
                        vs.append("'"+one[at].strip()+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())
    def import_file_byfixed(self,fname):
        csv_reader = csv.reader(open(fname,encoding=config_json.setting[config_json.setting2.value("sblx")]["encoding"]))
        data=[]
        for row in csv_reader:
            # logging.info(row)
            data.append(row)
        # logging.info(data)
        title=data[0]
        data=data[1:]
        logging.info(title)
        logging.info(data)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                try:
                    vs.append("'"+one[f["at"]]+"'")
                    fs.append("'"+f["name"]+"'")
                except IndexError:
                    pass
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError()) 
    def import_horibah(self,fname):
        csv_reader = csv.reader(open(fname,encoding=config_json.setting[config_json.setting2.value("sblx")]["encoding"]))
        data=[]
        for row in csv_reader:
            # logging.info(row)
            data.append(row)
        # logging.info(data)
        title=data[0]
        data=data[1:]
        logging.info(title)
        logging.info(data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    fs.append("'"+f["name"]+"'")
                    if f["name"] in ["LH"]:
                        vs.append(str(float(one[f["at"]])/10000.0))#ppm to %
                    else:
                        vs.append("'"+one[f["at"]]+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())                     
    def import_844(self,fname):
        csv_reader = csv.reader(open(fname,encoding=config_json.setting[config_json.setting2.value("sblx")]["encoding"]))
        data=[]
        for row in csv_reader:
            # logging.info(row)
            data.append(row)
        # logging.info(data)
        title=data[0]
        data=data[1:]
        logging.info(title)
        logging.info(data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    fs.append("'"+f["name"]+"'")
                    # if f["name"]=="time":
                    #     vs.append("'"+convert_time(one[f["at"]])+"'")
                    # else:
                    if f["name"] in ["LC","LS","LO","LN","m"]:
                        vs.append(one[f["at"]].split(" ")[0])
                    else:
                        vs.append("'"+one[f["at"]]+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())       
    def import_text_bytitle(self,txt):
        logging.info(txt)
        data=[]
        for row in txt.split("\n"):
            # logging.info(row)
            data.append(row.split("\t"))
        logging.info(data)
        title=data[0]
        data=data[1:]
        logging.info(title)
        logging.info(data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    fs.append("'"+f["name"]+"'")
                    # if f["name"]=="time":
                    #     vs.append("'"+convert_time(one[f["at"]])+"'")
                    # else:
                    vs.append("'"+one[f["at"]]+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())                                        
    def import_file_bytitle(self,fname):
        csv_reader = csv.reader(open(fname,encoding=config_json.setting[config_json.setting2.value("sblx")]["encoding"]))
        data=[]
        for row in csv_reader:
            # logging.info(row)
            data.append(row)
        # logging.info(data)
        title=data[0]
        data=data[1:]
        logging.info(title)
        logging.info(data)
        for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
            f["at"]=inList(f["title"],title)
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one)<2:
                continue
            fs=[]
            vs=[]
            for f in config_json.setting[config_json.setting2.value("sblx")]["fields"]:
                if f["at"]!=-1:
                    fs.append("'"+f["name"]+"'")
                    # if f["name"]=="time":
                    #     vs.append("'"+convert_time(one[f["at"]])+"'")
                    # else:
                    vs.append("'"+one[f["at"]]+"'")
            cmd="insert into result("+",".join(fs)+") values("+",".join(vs)+")"
            q=QSqlQuery()
            q.prepare(cmd)
            logging.info(cmd)
            r=q.exec_()
            if not r:
                logging.info(q.lastError())
    def importFieldStr(self,data):
        return data[0]
    def refresh_ff2(self):
        ff = config_json.setting["ff2"]
        self.ui.cmb_ff2.clear()
        self.ui.cmb_ff2.addItems(ff)
    def refresh_js2(self):
        ff = config_json.setting["js2"]
        self.ui.cmb_js2.clear()
        self.ui.cmb_js2.addItems(ff)

    def refresh_ffbh(self):
        if config_json.setting[config_json.setting2.value("sblx")]["cs"]:
            ff = config_json.setting["ffbh_cs"]
        else:
            ff = config_json.setting["ffbh_onh"]
        self.ui.cmb_ffbh.clear()
        self.ui.cmb_ffbh.addItems(ff)
        self.ui.cmb_ffbh.setCurrentIndex(config_json.setting["current"]["ffbh"])
        self.ui.cmb_ffbh.setEditable(True)
        self.completer = QCompleter(ff, self);
        self.completer.setFilterMode(Qt.MatchContains)
        self.completer.setCaseSensitivity(Qt.CaseInsensitive)
        self.completer.setCompletionMode(QCompleter.UnfilteredPopupCompletion);
        self.ui.cmb_ffbh.setCompleter(self.completer)
    def refresh_syy(self):
        ff = config_json.setting["syy"]
        self.ui.cmb_syy.clear()
        self.ui.cmb_syyqm.clear()
        self.ui.cmb_ysry.clear()
        for i in ff:
            self.ui.cmb_syy.addItem(i)
            self.ui.cmb_syyqm.addItem(i)
            self.ui.cmb_ysry.addItem(i)
        self.ui.cmb_syy.setCurrentIndex(config_json.setting["current"]["syy"])
        self.ui.cmb_ysry.setCurrentIndex(config_json.setting["current"]["ysry"])
        self.ui.cmb_syyqm.setCurrentIndex(config_json.setting["current"]["syyqm"])
    def genFieldStr(self):
        self.ncol=4
        r = "id,time ,sampleid,m"
        self.heads=["id","time","sampleid","m"]
        for f in config_json.setting[config_json.setting2.value("sblx")]["items"]:
            if f["used"]:
                r += "," + f["name"] 
                self.ncol+=1
                self.heads.append(f['name'])
        self.fieldStr=r

    def field_click(self, i):
        logging.info(i)
        config_json.setting[config_json.setting2.value("sblx")]["items"][i]["used"] = not config_json.setting[config_json.setting2.value("sblx")]["items"][i]["used"]
        logging.info(config_json.setting[config_json.setting2.value("sblx")]["items"])
        self.genFieldStr()
        self.refresh_grid()

    def refresh_grid(self):
        self.refresh_grid1()
        self.refresh_grid2()
    def refresh_grid1(self):
        self.model.setQuery(
            "select " + self.fieldStr + " from result where id") #" between '" + self.db_str + "' and '" + self.de_str + "' ")
        # self.ui.tableView.setColumnWidth(0, 50)
        self.ui.tableView.setColumnWidth(1, 150)
        self.ui.tableView.setColumnHidden(0,True)
        logging.info("col",self.ncol)
        for i in range(4,self.ncol):
            logging.info(i,'setIemtDelegate')
            self.ui.tableView.setItemDelegateForColumn(i, self.m_doubleDelegate)
    def refresh_grid2(self):
        cmd="select " + self.fieldStr + " from result where id in " + self.idStr #+" order by sampleid"
        logging.info(cmd)
        self.model2.setQuery(cmd)
        # self.ui.tableView.setColumnWidth(0, 50)
        self.ui.tableView_report.setColumnWidth(1, 150)
        self.ui.tableView_report.setColumnHidden(0,True)
        # for i in range(4,self.ncol):
        #     logging.info(i,'setIemtDelegate')
        #     self.ui.tableView_report.setItemDelegateForColumn(i, self.m_cBoxDelegate)
        self.model2.setHeaderData(self.ncol, Qt.Horizontal, "备注")
        for i in range(4,self.ncol):
            logging.info(i,'setIemtDelegate')
            self.model2.setHeaderData(i,Qt.Horizontal, self.heads[i])
            self.ui.tableView_report.setItemDelegateForColumn(i, self.m_doubleDelegate)
    def exit_app(self):
        pass
        # config_json.write_setting()
        self.close()

    def set_ffbh(self):
        from DlgArr import DlgArr
        if config_json.setting[config_json.setting2.value("sblx")]["cs"]:
            ff = config_json.setting["ffbh_cs"]
        else:
            ff = config_json.setting["ffbh_onh"]
        d = DlgArr(ff)
        r = d.exec_()
        if r == 1:
            ff=d.model.todos
            self.refresh_ffbh()
        else:
            pass
    def set_ff2(self):
        from DlgArr import DlgArr
        ff = config_json.setting["ff2"]
        d = DlgArr(ff)
        r = d.exec_()
        if r == 1:
            config_json.setting["ff2"]=d.model.todos
            self.refresh_ff2()
        else:
            pass
    def set_js2(self):
        from DlgArr import DlgArr
        ff = config_json.setting["js2"]
        d = DlgArr(ff)
        r = d.exec_()
        if r == 1:
            config_json.setting["js2"]=d.model.todos
            self.refresh_js2()
        else:
            pass            
    def set_syy(self):
        from DlgArr import DlgArr
        d = DlgArr(config_json.setting["syy"])
        r = d.exec_()
        if r == 1:
            config_json.setting["syy"]=d.model.todos
            self.refresh_syy()
        else:
            pass

    def setting(self):
        # from todo import DlgTodo
        # d=DlgTodo()
        # d.exec_()
        from dlgsetting import DlgSetting
        calculator = DlgSetting()
        r = calculator.exec_()
        # logging.info(r)s
        if r == 1:
            # self.set_database()
            self.ui.lineEdit_yqbh.setText(config_json.setting2.value("yqbh"))
            # self.setWindowTitle(u"\u6570\u636e\u4e0a\u62a5"+config_json.setting2.value("sblx"))
            self.label_sblx.setText(config_json.setting2.value("sblx"))
            self.resetItems()
        else:
            pass

    def checkBoth(self):
        name = []
        for f in config_json.setting[config_json.setting2.value("sblx")]["items"]:
            if f["used"]:
                if (f["name"][-1] in name):
                    QMessageBox.information(self, "", "不能同时选择一个元素的两个通道")
                    return True
                else:
                    name.append(f["name"][-1])
        return False

    def lims(self):
        if self.checkBoth():
            return
        if self.ui.lineEdit_filename.text() == "":
            file_name = config_json.setting2.value("yqbh") + "_" + timestr()
        else:
            file_name = self.ui.lineEdit_filename.text()
        path = config_json.setting2.value("output_folder")
        filepath = os.path.join(path, file_name)
        self.output_xlsx(filepath)

    def save_as(self):
        FileDialog = QFileDialog(self)
        FileDirectory = FileDialog.getSaveFileName(self)  # 选择目录，返回选中的路径
        fn = FileDirectory[0]
        if fn != "":
            self.output_xlsx(fn)

    def output_xlsx(self, filepath):
        n = self.model2.rowCount()
        if n == 0:
            return
        filepath = filepath + ".xlsx"
        # 1. 创建文件对象（指定文件名，模式，编码方式）a模式 为 下次写入在这次的下一行
        wb = openpyxl.Workbook()
        # 3. 构建列表头
        row = ["id", "m"]
        for f in config_json.setting[config_json.setting2.value("sblx")]["items"]:
            if f["used"]:
                row.append(f["name"][-1])
        row.append("备注")
        row.append("time")
        ws = wb.active
        ws.append(row)
        for row_idx in range(n):
            record = self.model2.record(row_idx)
            row = [record.value(record.indexOf("Sampleid"))]
            row.append(record.value(record.indexOf("m")))
            for f in config_json.setting[config_json.setting2.value("sblx")]["items"]:
                if f["used"]:
                    row.append(record.value(record.indexOf(f["name"])))
            row.append(self.model2.beizhu.get(row_idx))    
            row.append(record.value(record.indexOf("time")))
            ws.append(row)
        ffbh = self.ui.cmb_ffbh.currentText()
        syy = self.ui.cmb_syy.currentText()
        ysry = self.ui.cmb_ysry.currentText()
        yqbh = self.ui.lineEdit_yqbh.text()
        syyqm = self.ui.cmb_syyqm.currentText()
        ws.append(["ffbh&" + ffbh, "", "", "", ""])
        ws.append(["syy&" + syy, "", "", "", ""])
        ws.append(["ysry&" + ysry, "", "", "", ""])
        ws.append(["yqbh&" + yqbh, "", "", "", ""])
        ws.append(["syyqm&" + syyqm, "", "", "", ""])
        ws.append(["ff2&" + self.ui.cmb_ff2.currentText(), "", "", "", ""])#todo
        ws.append(["js2&" + self.ui.cmb_js2.currentText(), "", "", "", ""])
        ws.column_dimensions['A'].width=20.0
        wb.save(filepath)
        self.ui.textEdit.insertPlainText(filepath + "\n")
        scrollbar = self.ui.textEdit.verticalScrollBar()
        scrollbar.setSliderPosition(scrollbar.maximum())

        # self.ui.lineEdit_filename.setText("")

    # def output_csv(self, filepath):
    #     filepath = filepath + ".csv"
    #     n = self.model2.rowCount()
    #     if n == 0:
    #         return
    #     # 1. 创建文件对象（指定文件名，模式，编码方式）a模式 为 下次写入在这次的下一行
    #     with open(filepath, "w", encoding="utf-8", newline="") as f:
    #         # 2. 基于文件对象构建 csv写入对象
    #         csv_writer = csv.writer(f, dialect=Mydialect())  # Dialect.doublequote
    #         # 3. 构建列表头
    #         row = ["id", "m"]
    #         if self.lc or self.hc:
    #             row.append("c%")
    #         if self.ls or self.hs:
    #             row.append("s%")
    #         csv_writer.writerow(row)
    #         for row in range(n):
    #             record = self.model2.record(row)
    #             row = [record.value(record.indexOf("Sampleid")), record.value(record.indexOf("m"))]
    #             if self.lc:
    #                 row.append(record.value(record.indexOf("lc")))
    #             if self.hc:
    #                 row.append(record.value(record.indexOf("hc")))
    #             if self.ls:
    #                 row.append(record.value(record.indexOf("ls")))
    #             if self.hs:
    #                 row.append(record.value(record.indexOf("hs")))
    #             csv_writer.writerow(row)
    #         ffbh = self.ui.cmb_ffbh.currentText()
    #         syy = self.ui.cmb_syy.currentText()
    #         ysry = self.ui.cmb_ysry.currentText()
    #         yqbh = self.ui.lineEdit_yqbh.text()
    #         syyqm = self.ui.cmb_syyqm.currentText()
    #         row = ["ffbh&" + ffbh, "", "", ""]
    #         csv_writer.writerow(row)
    #         row = ["syy&" + syy, "", "", ""]
    #         csv_writer.writerow(row)
    #         row = ["ysry&" + ysry, "", "", ""]
    #         csv_writer.writerow(row)
    #         row = ["yqbh&" + yqbh, "", "", ""]
    #         csv_writer.writerow(row)
    #         row = ["syyqm&" + syyqm, "", "", ""]
    #         csv_writer.writerow(row)
    #         f.close()
    #         self.ui.lineEdit_filename.setText("")

    def gen_idstr(self):
        r = "("
        if len(self.ids)>0:
            for i in range(len(self.ids)):
                r +=  str(self.ids[i])  + ","
            r = r[:-1]
        r += ")"
        logging.info(r)
        self.idStr=r
    def clear(self):
        self.ids = []
        self.gen_idstr()
        self.model2.setQuery("select " + self.fieldStr + " from result where id in " + self.idStr)
        self.model2.beizhu={}

    def unselect(self):
        # logging.info("select")
        # logging.info(self.ids)
        selections = self.ui.tableView_report.selectionModel()
        list1 = selections.selectedIndexes()
        toremove = []
        for i in range(len(list1)):
            current = list1[i]
            # logging.info(current,dir(current))
            if current.column() == 0:
                row = current.row()
                record = self.model2.record(row)
                idCol = record.indexOf("id")  #
                id1 = record.value(idCol)
                toremove.append(id1)
        for i in toremove:
            try:
                self.ids.remove(i)
            except ValueError:
                pass
        self.gen_idstr()
        self.refresh_grid2()

    def select(self):
        # logging.info("select")
        selections = self.ui.tableView.selectionModel()
        list1 = selections.selectedIndexes()
        for i in range(len(list1)):
            current = list1[i]
            # logging.info(current,dir(current))
            if current.column() == 0:
                row = current.row()
                record = self.model.record(row)
                idCol = record.indexOf("id")  #
                id1 = record.value(idCol)
                if id1 in self.ids:
                    pass
                else:
                    self.ids.append(id1)
        self.gen_idstr()
        self.refresh_grid2()

    def begin_change(self, v):
        # logging.info(v)
        self.ui.radioButton_range.setChecked(True)

        de = self.ui.dateTimeEdit_end.dateTime()
        self.de_str = de.toString("yyyy-MM-dd hh:mm:ss")

        db = v
        self.db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        # logging.info(self.db_str)
        # logging.info(self.de_str)
        self.refresh_grid1()
        # if(self.old==-1):
        #     self.old=0;
        #     self.ui.tableView.selectRow(0);
        # else:
        #     self.ui.tableView.selectRow(self.old);        
        # pass   

    def end_change(self, v):
        # logging.info(v)
        self.ui.radioButton_range.setChecked(True)

        db = self.ui.dateTimeEdit_begin.dateTime()
        self.db_str = db.toString("yyyy-MM-dd hh:mm:ss")

        de = v
        self.de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        # logging.info(self.db_str)
        # logging.info(self.de_str)
        self.refresh_grid1()
        # if(self.old==-1):
        #     self.old=0;
        #     self.ui.tableView.selectRow(0);
        # else:
        #     self.ui.tableView.selectRow(self.old);        
        # pass   

    def day_change(self, v):
        # logging.info(v)
        self.ui.radioButton_day.setChecked(True)

        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))

        self.de_str = de.toString("yyyy-MM-dd hh:mm:ss")

        db = de.addDays(-v)
        db.setTime(QTime(0, 0, 0))

        self.db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        # logging.info(self.db_str)
        # logging.info(self.de_str)
        self.refresh_grid1()
        # if(self.old==-1):
        #     self.old=0;
        #     self.ui.tableView.selectRow(0);
        # else:
        #     self.ui.tableView.selectRow(self.old);        
        # pass

    def month_change(self, v):
        # logging.info(v)
        self.ui.radioButton_month.setChecked(True)

        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))

        self.de_str = de.toString("yyyy-MM-dd hh:mm:ss")

        db = de.addMonths(-v)
        db.setTime(QTime(0, 0, 0))

        self.db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        # logging.info(self.db_str)
        # logging.info(self.de_str)
        self.refresh_grid1()
        # if(self.old==-1):
        #     self.old=0;
        #     self.ui.tableView.selectRow(0);
        # else:
        #     self.ui.tableView.selectRow(self.old);

    def query(self):
        pass

    def day_change(self, v):
        # logging.info(v)
        self.days=v
        de = QDateTime.currentDateTime()
        de.setTime(QTime(23, 59, 59))
        self.de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        db = de.addDays(-v)
        db.setTime(QTime(0, 0, 0))
        self.db_str = db.toString("yyyy-MM-dd hh:mm:ss")            
if __name__ == "__main__":
    import sys
    a = QApplication(sys.argv)
    a.lastWindowClosed.connect(a.quit)
    w = MainW()
    w.show()
    a.exec_()